#/usr/bin/env python
# -*- coding: UTF-8 -*-

'''
Copyright (C) 2010 dbzhang800@gmail.com
All rights reserved.

last modified: 2010.07.21
''' 

import os
import os.path
import sys
from win32com.client import Dispatch

class MSExcel:
    def __init__(self, filename=""):
        self.xlsApp = Dispatch("Excel.Application")
        if filename:
            self.filename = os.path.abspath(filename)
            try:
                self.xlsBook = self.xlsApp.Workbooks.Open(self.filename)
            except:
                print "Warrning: %s not opened." % self.filename
                self.xlsBook = self.xlsApp.Workbooks.Add()
        else:
            self.filename = ""
            self.xlsBook = self.xlsApp.Workbooks.Add()

    def __del__(self):
        self.xlsBook.Close(SaveChanges=0)
        del self.xlsApp

    def save(self, filename=""):
        if filename:
            self.xlsBook.SaveAs(os.path.abspath(filename))
        elif self.filename:
            self.xlsBook.Save()
        else:
            self.xlsBook.SaveAs(os.path.abspath("untitled"))
            print "Warrning: file name not specialed, use 'untitled'." 

    def activatesheet(self, sheet):
        try:
            self.xlsBook.Worksheets(sheet).Activate()
        except:
            self.xlsBook.Sheets(sheet).Activate()

    def _sheet(self, sheet):
        try:
            cursheet = self.xlsBook.Worksheets(sheet)
        except:
            if sheet:
                print "Warrning: Sheet %s not opened." % sheet
            cursheet = self.xlsApp.ActiveSheet
        return cursheet

    def cell(self, row, col, sheet=None):
        return self._sheet(sheet).Cells(row, col).Value

    def setcell(self, row, col, value, sheet=None):
        self._sheet(sheet).Cells(row, col).Value = value

    def usedrowcount(self, sheet=None):
        return self._sheet(sheet).UsedRange.Rows.Count

    def usedcolcount(self, sheet=None):
        return self._sheet(sheet).UsedRange.Columns.Count

    def row(self, rownumber, sheet=None):
        try:
            res = self._sheet(sheet).UsedRange.Rows(rownumber).Value
        except:
            return []
        return [v for v in res[0]]

    def col(self, colnumber, sheet=None):
        try:
            res = self._sheet(sheet).UsedRange.Columns(colnumber).Value
        except:
            return []
        return [v[0] for v in res]

if __name__ ==  '__main__':
    xls = MSExcel(u"WD与物镜激励及FOV视场关系.xlsx")
    xls.xlsApp.Visible = 1
    print xls.cell(1,1)
    xls.setcell(4,4, 999)
    xls.save()
    print xls.row(1)
    print xls.col(1)
    raw_input("press enter to close...")
    del xls
    raw_input("press enter key...")
    #new file
    xls = MSExcel(u"")
    xls.xlsApp.Visible = 1
    raw_input("press enter key...")
